﻿using System;
using System.IO;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SpringStudio.Common;
using Microsoft.Office.Interop.Excel;

namespace Sdepci.InfoCode.Web.CodeDesign
{
    public partial class DsToExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.CreateExcel();
            }
        }

        public void CreateExcel()
        {
            DataSet ds = null;
            System.Data.DataTable dt = null;
            string fileName = string.Empty;
            if (Session["ExcelDataSet"] != null && Session["ExcelFileName"] != null)
            {
                ds = Session["ExcelDataSet"] as DataSet;
                fileName = Session["ExcelFileName"] as string;
                Session.Remove("ExcelDataSet");
                Session.Remove("ExcelFileName");
                dt = ds.Tables[0];
            }
            else
            {
                this.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "<script language=\"javascript\">alert(\"系统错误：无法获取数据！请与系统管理员联系。\");self.close();</script>");
                return;
            }

            // 在后台利用Excel.Application生成Excel，保存在临时目录
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = false;

            Microsoft.Office.Interop.Excel._Workbook book = excel.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel._Worksheet sheet = book.ActiveSheet as Microsoft.Office.Interop.Excel._Worksheet;

            //取得数据表各列标题
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                excel.Cells[1, i + 1] = dt.Columns[i].Caption.ToString();
            }
            sheet.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;  // 设置标题为粗体
            sheet.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;  // 设置标题为粗体

            //逐行处理数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    excel.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
                }
            }

            sheet.get_Range(excel.Cells[1, 1], excel.Cells[dt.Rows.Count + 1, dt.Columns.Count]).Columns.AutoFit();

            string temp = @"C:\WINDOWS\Temp\SdepciInfoCode.xlsx";
            book.SaveCopyAs(temp);
            book.Close(false, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();


            // 读取服务器上的文件，并输出文件内容以供下载
            if (File.Exists(temp))
            {
                FileInfo file = new FileInfo(temp);

                this.Response.Clear();
                this.Response.Buffer = true;
                this.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName) + "[" + DateTime.Now.ToString("yyyy-MM-dd_HH_mm_ss") + "].xlsx");
                this.Response.AppendHeader("Content-Length", file.Length.ToString());
                this.Response.ContentType = "application/vnd.ms-excel";

                this.Response.WriteFile(file.FullName);
                this.Response.End();
            }
        }
    }
}
